clear all
set more off

* Assume working directory is set to Project/

* Go into the Data/Input Data/BLSDATA/ folder
cd "Data/InputData/BLSDATA/"

* Get list of all Excel files in the directory
local files: dir "." files "*.xlsx"

* Create an empty dataset to append into
clear
tempname master
save `master', emptyok replace

foreach file of local files {
    * Extract state name from filename (remove ".xlsx")
    local state = subinstr("`file'", ".xlsx", "", .)

    * Import data, skipping metadata rows (data starts in row 11)
    import excel "`file'", sheet("BLS Data Series") cellrange(A11) firstrow clear

    * Rename variables
    rename Year year
    rename Period period
    rename laborforcepar~e labor_force_participation_rate
    rename employmentpop~o employment_population_ratio
    rename unemploymentr~e unemployment_rate

    * Add a new column for state name
    gen state = "`state'"

    * Convert 'Period' to numeric month
    gen month = .
    replace month = 1 if period == "Jan"
    replace month = 2 if period == "Feb"
    replace month = 3 if period == "Mar"
    replace month = 4 if period == "Apr"
    replace month = 5 if period == "May"
    replace month = 6 if period == "Jun"
    replace month = 7 if period == "Jul"
    replace month = 8 if period == "Aug"
    replace month = 9 if period == "Sep"
    replace month = 10 if period == "Oct"
    replace month = 11 if period == "Nov"
    replace month = 12 if period == "Dec"
    drop period

    * Append to master dataset
    append using `master', force
    save `master', replace
}

* Now move back to Project/ folder
cd "../../.."

* Save final dataset
save "Data/IntermediateData/unemployment_data.dta", replace

* Final check
summarize year month unemployment_rate
tab state
tab month
